﻿using FreeSql;
using Microsoft.Data.SqlClient;
using MiniJob;
using MiniJob.Entity;
using MiniJob.Orleans.SqlScripts;
using MySqlConnector;
using Npgsql;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Reflection;

namespace Microsoft.Extensions.DependencyInjection;

public static class FreeSqlExtension
{
    public static IServiceCollection AddFreeSql(this IServiceCollection services, IConfiguration configuration)
    {
        var dbProvider = Appsettings.DbProvider;
        var dbConn = Appsettings.DbConn;

        if (string.IsNullOrEmpty(dbProvider))
        {
            throw new ArgumentNullException(dbProvider);
        }
        if (string.IsNullOrEmpty(dbConn))
        {
            throw new ArgumentNullException(dbConn);
        }

        var dbType = ProviderToFreesqlDbType(dbProvider);
        if (!dbType.HasValue)
        {
            throw new ArgumentException(nameof(dbProvider), $"[{dbProvider}] is not a freesql supported provider.");
        }

        IFreeSql fsqlFactory(IServiceProvider service)
        {
            var loggerFactor = service.GetRequiredService<ILoggerFactory>();
            var logger = loggerFactor.CreateLogger("FreeSql");
            IFreeSql fsql = new FreeSqlBuilder()
                .UseConnectionString(dbType.Value, dbConn)
                .CreateDatabaseIfNotExists(logger)
                .InitOrleansAdoNetDb()
                .UseMonitorCommand(cmd => logger.LogInformation("Sql：{CommandText}", cmd.CommandText))
                .UseAutoSyncStructure(true) //自动同步实体结构到数据库，只有CRUD时才会生成表
                .Build();

            fsql.Aop.AuditValue += (s, e) =>
            {
                if (e.Object is Entity entity)
                {
                    entity.CreateTime = DateTime.Now;
                    entity.UpdateTime = DateTime.Now;
                }
            };

            return fsql;
        }
        services.AddSingleton<IFreeSql>(fsqlFactory);

        return services;
    }

    #region CreateDatabaseIfNotExists
    /// <summary>
    /// 请在UseConnectionString配置后调用此方法
    /// </summary>
    /// <param name="this"></param>
    /// <returns></returns>
    public static FreeSqlBuilder CreateDatabaseIfNotExists(this FreeSqlBuilder @this, ILogger logger)
    {
        FieldInfo? dataTypeFieldInfo = @this.GetType().GetField("_dataType", BindingFlags.NonPublic | BindingFlags.Instance)
            ?? throw new ArgumentException("_dataType is null");

        string? connectionString = GetConnectionString(@this)
            ?? throw new ArgumentNullException("ConnectionString is null");

        DataType dbType = (DataType)dataTypeFieldInfo.GetValue(@this)!;

        switch (dbType)
        {
            case DataType.MySql:
                return @this.CreateDatabaseIfNotExistsMySql(connectionString);
            case DataType.SqlServer:
                return @this.CreateDatabaseIfNotExistsSqlServer(connectionString);
            case DataType.PostgreSQL:
                return @this.CreateDatabaseIfNotExistsPgSql(connectionString);
            case DataType.Oracle:
                break;
            case DataType.Sqlite:
                return @this;
            case DataType.OdbcOracle:
                break;
            case DataType.OdbcSqlServer:
                break;
            case DataType.OdbcMySql:
                break;
            case DataType.OdbcPostgreSQL:
                break;
            case DataType.Odbc:
                break;
            case DataType.MsAccess:
                break;
            case DataType.Dameng:
                break;
            case DataType.ShenTong:
                break;
            case DataType.KingbaseES:
                break;
            case DataType.Firebird:
                break;
            case DataType.Custom:
                break;
            case DataType.ClickHouse:
                break;
            case DataType.GBase:
                break;
            default:
                break;
        }

        logger.LogError("不支持创建数据库:{DbType}", dbType);
        return @this;
    }
    #endregion

    #region MySql
    public static FreeSqlBuilder CreateDatabaseIfNotExistsMySql(this FreeSqlBuilder @this, string connectionString = "")
    {
        MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(connectionString);

        string createDatabaseSql = $"USE mysql;CREATE DATABASE IF NOT EXISTS `{builder.Database}` CHARACTER SET '{builder.CharacterSet}' COLLATE 'utf8mb4_general_ci'";

        using MySqlConnection cnn = new MySqlConnection($"Data Source={builder.Server};Port={builder.Port};User ID={builder.UserID};Password={builder.Password};Initial Catalog=mysql;Charset=utf8;SslMode=none;Max pool size=1");
        cnn.Open();
        using (MySqlCommand cmd = cnn.CreateCommand())
        {
            cmd.CommandText = createDatabaseSql;
            cmd.ExecuteNonQuery();
        }

        return @this;
    }
    #endregion

    #region SqlServer
    public static FreeSqlBuilder CreateDatabaseIfNotExistsSqlServer(this FreeSqlBuilder @this, string connectionString = "")
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
        string createDatabaseSql;
        if (!string.IsNullOrEmpty(builder.AttachDBFilename))
        {
            string fileName = ExpandFileName(builder.AttachDBFilename);
            string name = Path.GetFileNameWithoutExtension(fileName);
            string logFileName = Path.ChangeExtension(fileName, ".ldf");
            createDatabaseSql = @$"CREATE DATABASE {builder.InitialCatalog}   on  primary   
                (
                    name = '{name}',
                    filename = '{fileName}'
                )
                log on
                (
                    name= '{name}_log',
                    filename = '{logFileName}'
                )";
        }
        else
        {
            createDatabaseSql = @$"CREATE DATABASE {builder.InitialCatalog}";
        }

        using SqlConnection cnn = new SqlConnection($"Data Source={builder.DataSource};Integrated Security = True;User ID={builder.UserID};Password={builder.Password};Initial Catalog=master;Min pool size=1");
        cnn.Open();
        using SqlCommand cmd = cnn.CreateCommand();
        cmd.CommandText = $"select * from sysdatabases where name = '{builder.InitialCatalog}'";

        SqlDataAdapter apter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        apter.Fill(ds);

        if (ds.Tables[0].Rows.Count == 0)
        {
            cmd.CommandText = createDatabaseSql;
            cmd.ExecuteNonQuery();
        }

        return @this;
    }

    private static string ExpandFileName(string fileName)
    {
        if (fileName.StartsWith("|DataDirectory|", StringComparison.OrdinalIgnoreCase))
        {
            var dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
            if (string.IsNullOrEmpty(dataDirectory))
            {
                dataDirectory = AppDomain.CurrentDomain.BaseDirectory;
            }
            string name = fileName.Replace("\\", "").Replace("/", "").Substring("|DataDirectory|".Length);
            fileName = Path.Combine(dataDirectory, name);
        }
        if (!Directory.Exists(Path.GetDirectoryName(fileName)))
        {
            Directory.CreateDirectory(Path.GetDirectoryName(fileName)!);
        }
        return Path.GetFullPath(fileName);
    }

    #endregion

    #region PgSql
    public static FreeSqlBuilder CreateDatabaseIfNotExistsPgSql(this FreeSqlBuilder @this, string connectionString = "")
    {
        NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder(connectionString);

        string databaseExistSql = $"SELECT COUNT(*) FROM pg_database WHERE datname = '{builder.Database}'";

        string createDatabaseSql = $"CREATE DATABASE \"{builder.Database}\" WITH OWNER = \"{builder.Username}\"";

        using NpgsqlConnection cnn = new NpgsqlConnection($"Host={builder.Host};Port={builder.Port};Username={builder.Username};Password={builder.Password};Pooling=true");
        cnn.Open();

        using (NpgsqlCommand cmd = cnn.CreateCommand())
        {
            cmd.CommandText = databaseExistSql;
            bool databaseExist = Convert.ToInt32(cmd.ExecuteScalar()?.ToString()) > 0;
            if (databaseExist == false)
            {
                cmd.CommandText = createDatabaseSql;
                cmd.ExecuteNonQuery();
            }
        }


        return @this;
    }
    #endregion

    #region InitOrleansAdoNetDb
    public static FreeSqlBuilder InitOrleansAdoNetDb(this FreeSqlBuilder @this)
    {
        FieldInfo? dataTypeFieldInfo = @this.GetType().GetField("_dataType", BindingFlags.NonPublic | BindingFlags.Instance)
            ?? throw new ArgumentException("_dataType is null");

        string? connectionString = GetConnectionString(@this)
            ?? throw new ArgumentNullException("ConnectionString is null");

        DataType dbType = (DataType)dataTypeFieldInfo.GetValue(@this)!;

        switch (dbType)
        {
            case DataType.MySql:
                return @this.InitOrleansDbMySql(connectionString);
            case DataType.SqlServer:
                return @this.InitOrleansDbSqlServer(connectionString);
            case DataType.PostgreSQL:
                return @this.InitOrleansDbPgSql(connectionString);
            case DataType.Oracle:
                return @this.InitOrleansDbOracle(connectionString);
            default:
                break;
        }

        return @this;
    }

    public static FreeSqlBuilder InitOrleansDbMySql(this FreeSqlBuilder @this, string connectionString)
    {
        using MySqlConnection cnn = new(connectionString);
        cnn.Open();
        using (var cmd = cnn.CreateCommand())
        {
            cmd.CommandText = "SHOW TABLES LIKE 'OrleansQuery'";
            var tableName = cmd.ExecuteScalar();
            if (tableName == null)
            {
                foreach (var script in MySQLScript.Scripts)
                {
                    cmd.CommandText = script;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        return @this;
    }

    public static FreeSqlBuilder InitOrleansDbSqlServer(this FreeSqlBuilder @this, string connectionString)
    {
        using SqlConnection cnn = new(connectionString);
        cnn.Open();
        using (var cmd = cnn.CreateCommand())
        {
            cmd.CommandText = "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME = 'OrleansQuery'";
            var tableName = cmd.ExecuteScalar();
            if (tableName == null)
            {
                foreach (var script in SQLServerScript.Scripts)
                {
                    cmd.CommandText = script;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        return @this;
    }

    public static FreeSqlBuilder InitOrleansDbPgSql(this FreeSqlBuilder @this, string connectionString)
    {
        using NpgsqlConnection cnn = new(connectionString);
        cnn.Open();
        using (var cmd = cnn.CreateCommand())
        {
            cmd.CommandText = "select relname from pg_class where relname = 'orleansquery'";
            var tableName = cmd.ExecuteScalar();
            if (tableName == null)
            {
                foreach (var script in PostgreSQLScript.Scripts)
                {
                    cmd.CommandText = script;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        return @this;
    }

    public static FreeSqlBuilder InitOrleansDbOracle(this FreeSqlBuilder @this, string connectionString)
    {
        using OracleConnection cnn = new(connectionString);
        cnn.Open();
        using (var cmd = cnn.CreateCommand())
        {
            cmd.CommandText = "SELECT table_name FROM user_tables WHERE table_name = 'ORLEANSQUERY'";
            var tableName = cmd.ExecuteScalar();
            if (tableName == null)
            {
                foreach (var script in OracleScript.Scripts)
                {
                    cmd.CommandText = script;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        return @this;
    }
    #endregion

    private static string? GetConnectionString(this FreeSqlBuilder @this)
    {
        Type type = @this.GetType();
        FieldInfo? fieldInfo = type.GetField("_masterConnectionString", BindingFlags.NonPublic | BindingFlags.Instance)
            ?? throw new ArgumentException("_masterConnectionString is null");

        return fieldInfo.GetValue(@this)?.ToString();
    }

    public static DataType? ProviderToFreesqlDbType(string provider)
    {
        switch (provider.ToLower())
        {
            case "sqlite":
                return DataType.Sqlite;
            case "mysql":
                return DataType.MySql;
            case "sqlserver":
                return DataType.SqlServer;
            case "npgsql":
                return DataType.PostgreSQL;
            case "postgresql":
                return DataType.PostgreSQL;
            case "pg":
                return DataType.PostgreSQL;
            case "oracle":
                return DataType.Oracle;
            default:
                break;
        }

        return null;
    }
}
